package zy.dao.vip.clear.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import zy.dao.BaseDaoImpl;
import zy.dao.vip.clear.VipPointsClearDAO;
import zy.entity.vip.member.T_Vip_Member;
import zy.entity.vip.member.T_Vip_PonitList;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class VipPointsClearDAOImpl extends BaseDaoImpl implements VipPointsClearDAO {

	@Override
	public Integer count(Map<String, Object> params) {
		Object gd_name = params.get("gd_name");
		StringBuffer sql = new StringBuffer(""); 
		sql.append(" SELECT COUNT(1) ");
		sql.append(" FROM (");
		sql.append(" SELECT vm_id,vm_cardcode,vm_shop_code,sp_name,vm_name,mt_name,vm_mt_code,vm_points,vm_date,");
		sql.append(" vm_used_points,vm_sysdate,vm_manager_code,vm_times,vm_total_money,vm_mobile,");
		sql.append(" IFNULL(cycleConsume,0) AS cycleConsume,t.companyid,");
		sql.append(" (SELECT gd_name FROM t_vip_grade gread WHERE gread.companyid = t.companyid AND gd_lower<=IFNULL(cycleConsume,0) AND gd_upper>IFNULL(cycleConsume,0) LIMIT 0,1) AS gd_name");	
        sql.append(" FROM t_vip_member t ");
        sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid");
		sql.append(" JOIN t_vip_membertype mt ON mt.mt_code = t.vm_mt_code AND mt.companyid = t.companyid");
		sql.append(" LEFT JOIN (");
		sql.append(" SELECT cycleConsume, sh_vip_code,companyid");
		sql.append(" FROM");
		sql.append(" (SELECT IFNULL(SUM(sh_sell_money),0) AS cycleConsume, sh_vip_code,companyid");
		sql.append(" FROM t_sell_shop sh ");
		sql.append(" WHERE sh.sh_state IN ('0', '1')");
		sql.append(" AND LENGTH(sh.sh_vip_code)>0");
		sql.append(" AND DATE_FORMAT(sh.sh_date, '%Y-%m-%d') >= :runOffDate");
		sql.append(" AND sh.companyid = :companyid");
		sql.append(" GROUP BY sh_vip_code) as gradetab");
		sql.append(" ) as temp ON temp.sh_vip_code = t.vm_code AND temp.companyid = t.companyid ");
		sql.append( getWhereSql(params));
		sql.append(") AS clear_temp WHERE 1=1 ");
		if( StringUtil.isNotEmpty(gd_name)){
			sql.append(" AND INSTR(gd_name, :gd_name)>0");
		}
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_Member> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object gd_name = params.get("gd_name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT vm_id,vm_cardcode,vm_shop_code,sp_name as shop_name,vm_name,vm_sex,mt_name,vm_mt_code,vm_points,vm_date,gd_name,");
		sql.append(" vm_used_points,(vm_points-vm_used_points) as vm_last_points,vm_sysdate,vm_times,vm_total_money,vm_mobile, ");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = clear_temp.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM (");
		sql.append(" SELECT vm_id,vm_cardcode,vm_shop_code,sp_name,vm_name,vm_sex,mt_name,vm_mt_code,vm_points,vm_date,");
		sql.append(" vm_used_points,vm_sysdate,vm_manager_code,vm_times,vm_total_money,vm_mobile,");
		sql.append(" IFNULL(cycleConsume,0) AS cycleConsume,t.companyid,");
		sql.append(" (SELECT gd_name FROM t_vip_grade gread WHERE gread.companyid = t.companyid AND gd_lower<=IFNULL(cycleConsume,0) AND gd_upper>IFNULL(cycleConsume,0) LIMIT 0,1) AS gd_name");	
        sql.append(" FROM t_vip_member t ");
        sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid");
		sql.append(" JOIN t_vip_membertype mt ON mt.mt_code = t.vm_mt_code AND mt.companyid = t.companyid");
		sql.append(" LEFT JOIN (");
		sql.append(" SELECT cycleConsume, sh_vip_code,companyid");
		sql.append(" FROM");
		sql.append(" (SELECT IFNULL(SUM(sh_sell_money),0) AS cycleConsume, sh_vip_code,companyid");
		sql.append(" FROM t_sell_shop sh ");
		sql.append(" WHERE sh.sh_state IN ('0', '1')");
		sql.append(" AND LENGTH(sh.sh_vip_code)>0");
		sql.append(" AND DATE_FORMAT(sh.sh_date, '%Y-%m-%d') >= :runOffDate");
		sql.append(" AND sh.companyid = :companyid");
		sql.append(" GROUP BY sh_vip_code) as gradetab");
		sql.append(" ) as temp ON temp.sh_vip_code = t.vm_code AND temp.companyid = t.companyid ");
		sql.append( getWhereSql(params));
		sql.append(") AS clear_temp WHERE 1=1 ");
		if( StringUtil.isNotEmpty(gd_name)){
			sql.append(" AND INSTR(gd_name, :gd_name)>0");
		}
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY vm_sysdate DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Member.class));
	}

	@Override
	public Map<String, Object> sum(Map<String, Object> params) {
		Object gd_name = params.get("gd_name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT SUM(vm_points) as vm_points,SUM(vm_last_points) as vm_last_points,");
		sql.append(" SUM(vm_total_money) as vm_total_money,SUM(vm_times) as vm_times ");
		sql.append(" FROM (");
		sql.append(" SELECT vm_id,vm_cardcode,vm_shop_code,sp_name,vm_name,mt_name,vm_mt_code,vm_points,vm_date,");
		sql.append(" vm_used_points,(vm_points-vm_used_points) as vm_last_points,vm_sysdate,vm_manager_code,vm_times,vm_total_money,vm_mobile,");
		sql.append(" IFNULL(cycleConsume,0) AS cycleConsume,t.companyid,");
		sql.append(" (SELECT gd_name FROM t_vip_grade gread WHERE gread.companyid = t.companyid AND gd_lower<=IFNULL(cycleConsume,0) AND gd_upper>IFNULL(cycleConsume,0) LIMIT 0,1) AS gd_name");	
        sql.append(" FROM t_vip_member t ");
        sql.append(" JOIN t_base_shop sp ON sp.sp_code = t.vm_shop_code AND sp.companyid = t.companyid");
		sql.append(" JOIN t_vip_membertype mt ON mt.mt_code = t.vm_mt_code AND mt.companyid = t.companyid");
		sql.append(" LEFT JOIN (");
		sql.append(" SELECT cycleConsume, sh_vip_code,companyid");
		sql.append(" FROM");
		sql.append(" (SELECT IFNULL(SUM(sh_sell_money),0) AS cycleConsume, sh_vip_code,companyid");
		sql.append(" FROM t_sell_shop sh ");
		sql.append(" WHERE sh.sh_state IN ('0', '1')");
		sql.append(" AND LENGTH(sh.sh_vip_code)>0");
		sql.append(" AND DATE_FORMAT(sh.sh_date, '%Y-%m-%d') >= :runOffDate");
		sql.append(" AND sh.companyid = :companyid");
		sql.append(" GROUP BY sh_vip_code) as gradetab");
		sql.append(" ) as temp ON temp.sh_vip_code = t.vm_code AND temp.companyid = t.companyid ");
		sql.append( getWhereSql(params));
		sql.append(") AS clear_temp WHERE 1=1 ");
		if( StringUtil.isNotEmpty(gd_name)){
			sql.append(" AND INSTR(gd_name, :gd_name)>0");
		}
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	
	/**
	 * where条件
	 */
	private String getWhereSql(Map<String, Object> params){
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_manager_code = params.get("vm_manager_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object begin_last_points = params.get("begin_last_points");
		Object end_last_points = params.get("end_last_points");
		StringBuffer sqlWhere = new StringBuffer();
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sqlWhere.append(getShopSQL(shop_type, 0));
			sqlWhere.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sqlWhere.append(" WHERE 1 = 1");
			sqlWhere.append(" AND sp_code = :shop_code");
		}
		sqlWhere.append(" AND vm_state='0' ");
		if (StringUtil.isNotEmpty(vm_shop_code)) {
			sqlWhere.append(" AND vm_shop_code = :vm_shop_code ");
		}
		if (StringUtil.isNotEmpty(vm_mt_code)) {
			sqlWhere.append(" AND vm_mt_code = :vm_mt_code ");
		}
		if (StringUtil.isNotEmpty(vm_cardcode)) {
			sqlWhere.append(" AND INSTR(vm_cardcode, :vm_cardcode) > 0 ");
		}
		if( StringUtil.isNotEmpty(begin_last_points) && StringUtil.isNotEmpty(end_last_points) ){
			sqlWhere.append(" AND (vm_points-vm_used_points) >= :begin_last_points");
			sqlWhere.append(" AND (vm_points-vm_used_points) <= :end_last_points");
		}
		if (StringUtil.isNotEmpty(vm_manager_code)) {
			sqlWhere.append(" AND vm_manager_code = :vm_manager_code ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sqlWhere.append(" AND vm_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sqlWhere.append(" AND vm_date <= :enddate ");
		}
		sqlWhere.append(" AND t.companyid=:companyid");
		return sqlWhere.toString();
	}

	@Override
	public List<T_Vip_Member> getMemberByIds(Map<String, Object> params) {
		Object idsObj = params.get("ids");
		if(StringUtil.isNotEmpty(idsObj) ){
			String ids = (String)idsObj;
			String[] _ids = ids.split(",");
			StringBuffer sql = new StringBuffer("");
			sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_password,vm_sex,");
			sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,companyid,");
			sql.append(" vm_total_point,vm_points,vm_used_points,(vm_points-vm_used_points) as vm_last_points,vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money");
			sql.append(" FROM t_vip_member t");
			sql.append(" WHERE 1 = 1");
			sql.append(" AND t.vm_id in (");
			if( _ids.length > 0 ){
				String sqlWhere = "";
				for( String id : _ids ){
					if(StringUtil.isNotEmpty(id)){
						sqlWhere += id+",";
					}
				}
				sqlWhere = sqlWhere.substring(0,sqlWhere.length()-1);
				sql.append(sqlWhere);
			}
			sql.append(")");
			return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Member.class));
		}
		return null;
	}

	@Override
	@Transactional
	public void clear_points(Map<String, Object> params) {
		String shop_name = (String)params.get("shop_name");
		String us_name = (String)params.get("us_name");
		Integer companyid = (Integer)params.get(CommonUtil.COMPANYID);
		List<T_Vip_Member> memberList = (List<T_Vip_Member>)params.get("memberList");
		StringBuffer sql = new StringBuffer();
		//更新会员积分
		sql.append(" UPDATE t_vip_member SET vm_used_points = vm_points ");
		sql.append(" WHERE vm_id = :vm_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(memberList.toArray()));
		//插入积分明细表
		if(memberList!=null && memberList.size()>0){
			List<T_Vip_PonitList> pointList = new ArrayList<T_Vip_PonitList>();
			T_Vip_PonitList t_Vip_PonitList = null;
			for(int i=0;i<memberList.size();i++){
				t_Vip_PonitList = new T_Vip_PonitList();
				t_Vip_PonitList.setVpl_vm_code(memberList.get(i).getVm_code());
				t_Vip_PonitList.setVpl_point(-memberList.get(i).getVm_last_points());
				t_Vip_PonitList.setVpl_type(7);
				t_Vip_PonitList.setVpl_remark("清除会员"+memberList.get(i).getVm_last_points()+"积分");
				t_Vip_PonitList.setVpl_date(DateUtil.getCurrentTime());
				t_Vip_PonitList.setVpl_shop_name(shop_name);
				t_Vip_PonitList.setVpl_manager(us_name);
				t_Vip_PonitList.setCompanyid(companyid);
				pointList.add(t_Vip_PonitList);
			}
			sql.setLength(0);
			sql.append("INSERT INTO t_vip_pointlist");
			sql.append("(");
			sql.append("vpl_vm_code,vpl_point,vpl_type,vpl_remark,vpl_date,vpl_shop_name,vpl_manager,companyid");
			sql.append(") VALUES (");
			sql.append(":vpl_vm_code,:vpl_point,:vpl_type,:vpl_remark,:vpl_date,:vpl_shop_name,:vpl_manager,:companyid");
			sql.append(")");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(pointList.toArray()));
		}
	}

}
